package com.ybg.engine.report.sign;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 * 点名统计dao
 * 
 * @author lishiwei
 * @data 2017年5月23日
 *
 */
public class SignReportDao {

	private JdbcTemplate jdbcTemplate;

	public SignReportDao(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	/**
	 * 出勤人次
	 * 
	 * @param time
	 *            yyyy-MM-dd or yyyy-MM
	 */
	public long countAttendanceNum(String time) {
		String querySql = "SELECT count(*) AS total FROM YBG_V_SIGN_COUNT where DQZT in ('1','2') and DMSJ like ? "
				+ " and DMID in " + getValidSignsSQL();

		return jdbcTemplate.queryForObject(querySql, Long.class, time + "%");
	}

	/**
	 * 出勤人次
	 * 
	 * @param schoolYear
	 *            学年
	 * @param schoolTerm
	 *            学期
	 * @param cycle
	 *            第几周
	 */
	public long countAttendanceNum(String schoolYear, String schoolTerm, String cycle) {
		String querySql = "SELECT count(*) AS total FROM YBG_V_SIGN_COUNT where DQZT in ('1','2') and XN = ? and XQ = ? and DJZ = ? "
				+ "and DMID in " + getValidSignsSQL();
		Object[] args = { schoolYear, schoolTerm, cycle };
		return jdbcTemplate.queryForObject(querySql, Long.class, args);
	}

	/* 缺勤人次 */
	public long countAbsenceNum(String time) {
		String querySql = "SELECT count(*) as total FROM YBG_V_SIGN_COUNT where DQZT in ('5')"
				+ " and DMSJ like ? and DMID in " + getValidSignsSQL();

		return jdbcTemplate.queryForObject(querySql, Long.class, time + "%");
	}

	/* 缺勤人次 */
	public long countAbsenceNum(String schoolYear, String schoolTerm, String cycle) {
		String querySql = "SELECT count(*) AS total FROM YBG_V_SIGN_COUNT WHERE DQZT in ('5') and XN = ? and XQ = ? and DJZ = ? "
				+ " and DMID in " + getValidSignsSQL();
		Object[] args = { schoolYear, schoolTerm, cycle };
		return jdbcTemplate.queryForObject(querySql, Long.class, args);
	}

	public long countUsageCourseNum(String time) {
		String querySql = "select count(*) AS total from YBG_V_SIGN_COUNT where DMSJ like ? and DMID in "
				+ getValidSignsSQL();
		return jdbcTemplate.queryForObject(querySql, Long.class, time + "%");
	}

	public long countUsageCourseNum(String schoolYear, String schoolTerm, String cycle) {
		String querySql = "SELECT count(*) AS total from YBG_V_SIGN_COUNT WHERE XN = ? and XQ = ? and DJZ = ? "
				+ "and DMID in " + getValidSignsSQL();
		Object[] args = { schoolYear, schoolTerm, cycle };
		return jdbcTemplate.queryForObject(querySql, Long.class, args);
	}

	public long countCourseNum(String schoolYear, String schoolTerm, String cycle) {

		return countCourseNum(schoolYear, schoolTerm, cycle, "1", "7");
	}

	public long countCourseNum(String schoolYear, String schoolTerm, String cycle, String week) {

		return countCourseNum(schoolYear, schoolTerm, cycle, week, week);
	}

	public long countCourseNum(String schoolYear, String schoolTerm, String cycle, String startWeek, String endWeek) {
		String querySql = "SELECT count(*) AS total from YBG_B_SCHEDULE_TEACHER t where XN=? and XQ=? and "
				+ matchCycleSQL(Integer.parseInt(cycle));
		List<String> args = new ArrayList<>();
		args.add(schoolYear);
		args.add(schoolTerm);

		// 星期几
		// if startWeek.equals(endWeek) "and xqj = ?"
		if (!StringUtils.isEmpty(startWeek)) {
			querySql = querySql + " and xqj >=? ";
			args.add(startWeek);
		}
		if (!StringUtils.isEmpty(endWeek)) {
			querySql = querySql + " and xqj <=? ";
			args.add(endWeek);
		}
		return jdbcTemplate.queryForObject(querySql, Long.class, args.toArray());
	}

	public long countUsageTeacherNum(String time) {
		String querySql = "select count(distinct(GH)) as total from YBG_V_SIGN_COUNT where DQZT is not null and DMSJ like ? "
				+ "and DMID in " + getValidSignsSQL();
		return jdbcTemplate.queryForObject(querySql, Long.class, time + "%");
	}

	public long countUsageTeacherNum(String schoolYear, String schoolTerm, String cycle) {
		String querySql = "SELECT count(distinct(GH)) as total from YBG_V_SIGN_COUNT where DQZT is not null and XN = ? and XQ = ? and DJZ = ? "
				+ "and DMID in " + getValidSignsSQL();
		Object[] args = { schoolYear, schoolTerm, cycle };
		return jdbcTemplate.queryForObject(querySql, Long.class, args);
	}

	public long countUsageStudentNum(String time) {
		String querySql = "SELECT count(*) AS total FROM YBG_V_SIGN_COUNT where DQZT is not null and DMSJ like ? "
				+ " and DMID in " + getValidSignsSQL();
		return jdbcTemplate.queryForObject(querySql, Long.class, time + "%");
	}

	public long countUsageStudentNum(String schoolYear, String schoolTerm, String cycle) {
		String querySql = "SELECT count(*) AS total from YBG_V_SIGN_COUNT where DQZT is not null and XN = ? and XQ = ? and DJZ = ? "
				+ " and DMID in " + getValidSignsSQL();
		Object[] args = { schoolYear, schoolTerm, cycle };
		return jdbcTemplate.queryForObject(querySql, Long.class,args);
	}

	private String getValidSignsSQL() {
		String sql = "((SELECT t1.DMID FROM (SELECT DMID,count(DQZT) as total from YBG_V_SIGN_COUNT GROUP BY DMID) t1 WHERE t1.total>0)) ";
		return sql;
	}

	private String matchCycleSQL(int cycle) {
		// 起始结束周匹配
		String baseCondition = " (to_number(substr(qsjsz,1,instr(qsjsz,'-',1,1)-1))<= '" + cycle + "' and '" + cycle
				+ "' <=to_number(substr(qsjsz,instr(qsjsz,'-',1,1)+1,3)) or instr(qsjsz,'" + cycle + "')<>0) ";

		// 单周
		String single_condition = "1=0";
		if (cycle % 2 == 1) {
			single_condition = "(dsz = 1 and 1=1 )";
		} else {
			single_condition = "(dsz = 1 and 1=0 )";
		}

		// 双周
		String double_condition = "1=0";
		if (cycle % 2 == 0) {
			double_condition = "(dsz = 2 and 1=1 )";
		} else {
			double_condition = "(dsz = 2 and 1=0 )";
		}

		return baseCondition + " AND " + "((dsz = 0) or " + single_condition + " or " + double_condition + ")";
	}

}
